package org.cnam.mobileShopping.sql;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbAdapter extends SQLiteOpenHelper {

	/* CONSTANTES */
	private final static String DATABASE_NAME = "mobileShopping";
	private final static int DATABASE_VERSION = 24;

	private final static String CREATE_USER = "CREATE TABLE user (userid INTEGER PRIMARY KEY AUTOINCREMENT,login VARCHAR(64),pwd VARCHAR(64),pseudo VARCHAR(64), commonid int);";
	private final static String CREATE_LIST = "CREATE TABLE mList (mlistid INTEGER PRIMARY KEY AUTOINCREMENT,label VARCHAR (64),createdatetime DATE,status smallint, commonid CHAR(36), version int, synchr int);";
	private final static String CREATE_USERLIST = "CREATE TABLE userlist (usermlistid INTEGER PRIMARY KEY AUTOINCREMENT,userid int,mlistid int references mList,status smallint);";
	private final static String CREATE_CATEGORY = "CREATE TABLE category (categoryid INTEGER PRIMARY KEY AUTOINCREMENT,label varchar(64) NOT NULL);";
	private final static String CREATE_PRODUCT = "CREATE TABLE product (productid INTEGER PRIMARY KEY AUTOINCREMENT,description varchar(64),barcode varchar(100),categoryid int references category);";
	private final static String CREATE_PRODUCTLIST = "CREATE TABLE productlist (productlistid INTEGER PRIMARY KEY AUTOINCREMENT,productid int,mlistid int,quantity int NOT NULL,statusid int,userid int, commonid char(36), version int, synchr int);";
	private final static String CREATE_UPDATE = "CREATE TABLE updateApp (time DATE);";

	/**
	 * Constructeur par défaut de la super class
	 * 
	 * @param context
	 *            L'activity courante
	 */
	public DbAdapter(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Creation de la base de données avec des if Exists évidemment
		db.execSQL(CREATE_USER);
		db.execSQL(CREATE_LIST);
		db.execSQL(CREATE_USERLIST);
		db.execSQL(CREATE_CATEGORY);
		db.execSQL(CREATE_PRODUCT);
		db.execSQL(CREATE_PRODUCTLIST);
		db.execSQL(CREATE_UPDATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS user");
		db.execSQL("DROP TABLE IF EXISTS mList");
		db.execSQL("DROP TABLE IF EXISTS userlist");
		db.execSQL("DROP TABLE IF EXISTS category");
		db.execSQL("DROP TABLE IF EXISTS product");
		db.execSQL("DROP TABLE IF EXISTS productlist");
		db.execSQL("DROP TABLE IF EXISTS updateApp");
		db.execSQL(CREATE_USER);
		db.execSQL(CREATE_LIST);
		db.execSQL(CREATE_USERLIST);
		db.execSQL(CREATE_CATEGORY);
		db.execSQL(CREATE_PRODUCT);
		db.execSQL(CREATE_PRODUCTLIST);
		db.execSQL(CREATE_UPDATE);
	}
}
